"""
数据源管理数据模型
遵循DataPlexus现有的数据库设计规范
"""

from sqlalchemy import Column, BigInteger, String, Boolean, DateTime, Text, JSON, ForeignKey, Integer, Enum as SQLEnum, Index
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from datetime import datetime
from enum import Enum
from typing import List, Optional
from app.core.database import Base
from app.utils.timezone_utils import get_shanghai_now


class DatabaseType(str, Enum):
    """数据库类型枚举"""
    MYSQL = "mysql"
    POSTGRESQL = "postgresql"
    ORACLE = "oracle"
    SQLSERVER = "sqlserver"
    DORIS = "doris"
    KAFKA = "kafka"


class TestStatus(str, Enum):
    """测试状态枚举"""
    SUCCESS = "success"
    FAILED = "failed"
    UNKNOWN = "unknown"


class DataSource(Base):
    """数据源模型"""
    __tablename__ = "dp_datasources"

    id = Column(BigInteger, primary_key=True, autoincrement=True, comment='数据源ID')
    name = Column(String(100), unique=True, nullable=False, comment='数据源名称（唯一标识）')
    display_name = Column(String(100), nullable=False, comment='显示名称')
    description = Column(Text, comment='数据源描述')
    type = Column(SQLEnum(DatabaseType), nullable=False, comment='数据库类型')
    host = Column(String(255), nullable=False, comment='主机地址')
    port = Column(Integer, nullable=False, comment='端口号')
    database_name = Column(String(100), nullable=False, comment='数据库名称')
    username = Column(String(100), nullable=False, comment='用户名')
    password_encrypted = Column(Text, nullable=False, comment='加密后的密码')
    connection_params = Column(JSON, comment='额外连接参数')
    is_active = Column(Boolean, default=True, comment='是否启用')
    connection_timeout = Column(Integer, default=30, comment='连接超时时间（秒）')
    max_connections = Column(Integer, default=10, comment='最大连接数')
    last_test_at = Column(DateTime, comment='最后测试时间')
    last_test_status = Column(SQLEnum(TestStatus), default=TestStatus.UNKNOWN, comment='最后测试状态')
    last_test_message = Column(Text, comment='最后测试消息')
    
    # 逻辑删除字段
    is_deleted = Column(Boolean, default=False, comment='是否已删除')
    deleted_at = Column(DateTime, comment='删除时间')
    deleted_by = Column(BigInteger, ForeignKey('dp_users.id', ondelete='SET NULL'), comment='删除者ID')
    
    # 审计字段
    created_by = Column(BigInteger, ForeignKey('dp_users.id', ondelete='SET NULL'), comment='创建者ID')
    updated_by = Column(BigInteger, ForeignKey('dp_users.id', ondelete='SET NULL'), comment='更新者ID')
    created_at = Column(DateTime, default=get_shanghai_now, comment='创建时间')
    updated_at = Column(DateTime, default=get_shanghai_now, onupdate=get_shanghai_now, comment='更新时间')

    # 关联关系
    test_logs = relationship("DataSourceTestLog", back_populates="datasource", cascade="all, delete-orphan")
    usage_stats = relationship("DataSourceUsageStats", back_populates="datasource", cascade="all, delete-orphan")
    
    # 创建者和更新者关联
    creator = relationship("User", foreign_keys=[created_by], backref="created_datasources")
    updater = relationship("User", foreign_keys=[updated_by], backref="updated_datasources")
    deleter = relationship("User", foreign_keys=[deleted_by], backref="deleted_datasources")

    # 索引
    __table_args__ = (
        Index('idx_dp_datasources_name', 'name'),
        Index('idx_dp_datasources_type', 'type'),
        Index('idx_dp_datasources_is_active', 'is_active'),
        Index('idx_dp_datasources_is_deleted', 'is_deleted'),
        Index('idx_dp_datasources_created_at', 'created_at'),
        Index('idx_dp_datasources_last_test_status', 'last_test_status'),
    )

    def __repr__(self):
        return f"<DataSource(id={self.id}, name='{self.name}', type='{self.type}')>"

    @property
    def connection_string_masked(self) -> str:
        """获取脱敏的连接字符串"""
        return f"{self.type}://{self.username}:***@{self.host}:{self.port}/{self.database_name}"

    @property
    def is_healthy(self) -> bool:
        """判断数据源是否健康"""
        return (
            self.is_active and 
            not self.is_deleted and 
            self.last_test_status == TestStatus.SUCCESS
        )

    def soft_delete(self, deleted_by_user_id: int):
        """软删除数据源"""
        self.is_deleted = True
        self.deleted_at = get_shanghai_now()
        self.deleted_by = deleted_by_user_id
        self.is_active = False

    def restore(self):
        """恢复已删除的数据源"""
        self.is_deleted = False
        self.deleted_at = None
        self.deleted_by = None

    def update_test_result(self, status: TestStatus, message: str = None):
        """更新测试结果"""
        self.last_test_at = get_shanghai_now()
        self.last_test_status = status
        self.last_test_message = message


class DataSourceTestLog(Base):
    """数据源测试日志模型"""
    __tablename__ = "dp_datasource_test_logs"

    id = Column(BigInteger, primary_key=True, autoincrement=True, comment='日志ID')
    datasource_id = Column(BigInteger, ForeignKey('dp_datasources.id', ondelete='CASCADE'), nullable=False, comment='数据源ID')
    test_status = Column(SQLEnum(TestStatus), nullable=False, comment='测试状态')
    test_message = Column(Text, comment='测试消息')
    response_time = Column(Integer, comment='响应时间（毫秒）')
    error_details = Column(JSON, comment='错误详情')
    test_params = Column(JSON, comment='测试参数')
    
    # 审计字段
    tested_by = Column(BigInteger, ForeignKey('dp_users.id', ondelete='SET NULL'), comment='测试者ID')
    tested_at = Column(DateTime, default=get_shanghai_now, comment='测试时间')

    # 关联关系
    datasource = relationship("DataSource", back_populates="test_logs")
    tester = relationship("User", backref="datasource_test_logs")

    # 索引
    __table_args__ = (
        Index('idx_dp_datasource_test_logs_datasource_id', 'datasource_id'),
        Index('idx_dp_datasource_test_logs_test_status', 'test_status'),
        Index('idx_dp_datasource_test_logs_tested_at', 'tested_at'),
    )

    def __repr__(self):
        return f"<DataSourceTestLog(id={self.id}, datasource_id={self.datasource_id}, status='{self.test_status}')>"


class DataSourceUsageStats(Base):
    """数据源使用统计模型"""
    __tablename__ = "dp_datasource_usage_stats"

    id = Column(BigInteger, primary_key=True, autoincrement=True, comment='统计ID')
    datasource_id = Column(BigInteger, ForeignKey('dp_datasources.id', ondelete='CASCADE'), nullable=False, comment='数据源ID')
    usage_date = Column(DateTime, nullable=False, comment='使用日期')
    connection_count = Column(Integer, default=0, comment='连接次数')
    query_count = Column(Integer, default=0, comment='查询次数')
    error_count = Column(Integer, default=0, comment='错误次数')
    avg_response_time = Column(Integer, comment='平均响应时间（毫秒）')
    
    # 审计字段
    created_at = Column(DateTime, default=get_shanghai_now, comment='创建时间')
    updated_at = Column(DateTime, default=get_shanghai_now, onupdate=get_shanghai_now, comment='更新时间')

    # 关联关系
    datasource = relationship("DataSource", back_populates="usage_stats")

    # 索引
    __table_args__ = (
        Index('idx_dp_datasource_usage_stats_datasource_id', 'datasource_id'),
        Index('idx_dp_datasource_usage_stats_usage_date', 'usage_date'),
    )

    def __repr__(self):
        return f"<DataSourceUsageStats(id={self.id}, datasource_id={self.datasource_id}, date='{self.usage_date}')>"
